﻿using ARS.Common.Entitys;
using ARS.Common.Helper;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Net;
using System.Net.Http;
using System.Security.Cryptography;
using System.Text;
using System.Threading;

namespace ARS.Common.Service
{
    public static class BuilderServices
    {
        private static string _PgCoonstr = "";
        private static string keyseed = "Ada4e1VL5L3q0Bq7XF6p";
        /// <summary>
        /// 初始化
        /// </summary>
        /// <param name="coonStr">ARS的数据库链接地址</param>
        public static void Init(string coonStr)
        {
            _PgCoonstr = coonStr;
        }

        /// <summary>
        /// 返回数据
        /// </summary>
        /// <param name="execInstance">执行实例</param>
        /// <param name="coonStr">数据库链接字符串</param>
        /// <param name="script">SQL 语句</param>
        /// <param name="dsName">Dataset 名字</param>
        /// <param name="tableNames">datatables 名字</param>
        /// <returns></returns>
        public static Tuple<DataSet, bool, string> ExecSqlScript(IExecScript execInstance, string coonStr, string script, string dsName = null, string[] tableNames = null)
        {
            try
            {
                var tds = execInstance.ExecSql(coonStr, script);
                if (!tds.Item2)
                {
                    return Tuple.Create(tds.Item1, false, $"message:{tds.Item3}");
                }

                var ds = tds.Item1;
                {
                    if (ds != null && ds.Tables.Count > 0)
                    {
                        if (ds.Tables.Count <= tableNames.Length)
                        {
                            for (var i = 0; i < ds.Tables.Count; i++)
                            {
                                ds.Tables[i].TableName = tableNames[i];
                            }
                        }
                        else
                        {
                            for (var i = 0; i < ds.Tables.Count; i++)
                            {
                                ds.Tables[i].TableName = $"sheet{i + 1}";
                            }
                        }
                    }
                    ds.DataSetName = string.IsNullOrEmpty(dsName) ? "数据表" : dsName;
                    return Tuple.Create(ds, true, "返回成功");
                }
            }
            catch (Exception ex)
            {
                var ds = new DataSet();
                return Tuple.Create(ds, false, $"message:{ex.Message}  StackTrace:{ex.StackTrace}");
            }
        }

        /// <summary>
        /// 获取匹配码 
        /// </summary>
        /// <param name="times">时序量</param>
        /// <param name="contents">内容值</param>
        /// <param name="deword">解密码</param>
        /// <returns>交叉反位 </returns>
        public static string MatchCode(string times,string contents,string deword)
        {
            var data= Helper.StringHelper.MD5($"{contents}-{times}-{deword}-{keyseed}");
            return Helper.StringHelper.EncryptInvert(data, new uint[] { 1, 4, 7, 9, 11, 16 });
        }

        /// <summary>
        /// 执行整个流程 发送数据
        /// </summary>
        public static void Run(int etId, IList<KeyValuePair<string, string>> _params, string logPath, Tuple<bool, string, string> hooks = null,string linkurl= "https://ars.eyeslive.net/ars/files")
        {
            Log.WriteLine($"入参 邮件模板编号:{etId}  {JsonHelper.ToJsonString(_params)}");
            Log.WriteLine($"开始执行邮件发送任务 邮件模板编号:{etId}");
            var dataHelper = new DataRepository(_PgCoonstr);
            var emailtempInfo = dataHelper.GetEmailTemplate(etId);
            var start = DateTime.Now;
            if (emailtempInfo == null)
            {
                Log.WriteLine($"未找到邮件模板，无法执行该任务，发送终止");
                return;
            }
            var txtsqlscript = emailtempInfo.SqlScript;
            foreach (KeyValuePair<string, string> _pinfo in _params)
            {
                txtsqlscript = txtsqlscript.Replace(_pinfo.Key, _pinfo.Value);
                // Log.WriteLine($"sqlscript  Replace  ----------- {_pinfo.Key} {_pinfo.Value}",ConsoleColor.White,false);
            }
            var _t = "-- ";
            Log.WriteLine($"执行的脚本\r\n {_t.PadRight(30, '-')}");
            Log.WriteLine($"{txtsqlscript}\r\n", ConsoleColor.White, false);
            Log.WriteLine($"{_t.PadRight(30, '-')}\r\n");
            if (!emailtempInfo.IsEnable)
            {
                Log.WriteLine($"邮件编号:{etId}  的邮件模板被禁用，无法执行该任务，发送终止");
                return;
            }

            try
            {

                Log.WriteLine($"邮件模板id={emailtempInfo.Id} 名称={emailtempInfo.Name} 已经获取该邮件模板信息");
                var sqlInstanceInfo = dataHelper.GetSQlInstance(emailtempInfo.ExecInstance);
                if (sqlInstanceInfo == null || sqlInstanceInfo.DbType == (int)Sqltype.Unknown)
                {
                    Log.WriteLine($"邮件编号:{etId} 的邮件模板被禁用，无法执行该任务，发送终止");
                    return;
                }
                Log.WriteLine($"邮件模板id={emailtempInfo.Id} 名称={emailtempInfo.Name} 已经获取该邮件模板 数据库执行实例");
                IExecScript execScript = null;
                switch ((Sqltype)sqlInstanceInfo.DbType)
                {
                    case Sqltype.Postgres:
                        {
                            execScript = new ExecScripts.PgExecScript();
                        }
                        break;
                    case Sqltype.SqlServer:
                        {
                            execScript = new ExecScripts.SqlExecScript();
                        }
                        break;
                    case Sqltype.Unknown:
                    default:
                        {

                        }
                        break;
                }
                if (execScript == null)
                {
                    Log.WriteLine($"邮件模板id={emailtempInfo.Id} 名称={emailtempInfo.Name} 已经获取该邮件模板 数据库执行实例生成失败，无法执行数据操作");
                    return;
                }
                //替换 SQL变量，开始执行数据



                //解密Sql 字符串
                var dbString = StringHelper.DeEncrypt(sqlInstanceInfo.DBstring);
                //Log.WriteLine($"解密Sql 字符串\r\n {dbString}");

                var isRetry = 3;
            retrypoint:
                var result = ExecSqlScript(execScript, dbString, txtsqlscript, emailtempInfo.Name, emailtempInfo.SheetNames);

                // Log.WriteLine($"获取结果： Tables.Count={result.Item1.Tables.Count}  带了自动错误报警");
                if (!result.Item2 || result.Item1.Tables.Count < 1)
                {
                    //   var _logPath = Path.Combine(logPath, "logs", "exception.log");
                    var message = $"etid={etId} 任务执行失败 原因:{result.Item3}  \r\nExecTime:{DateTime.Now:YYYY-MM-dd HH:mm:ss}\r\n";
                    Log.WriteLine(message);
                    //正在尝试重试,,计算共重试一次
                    if (isRetry>=1)
                    {
                        isRetry--;
                        Log.WriteLine($"\r\n {"=".PadRight(60, '-')}\r\n 执行出现异常 休息30s,正在重试一次\r\n \r\n {"=".PadRight(60, '-')}");
                        Thread.Sleep(30 * 1000);
                        goto retrypoint;
                    }

                    //  FileWriteLine(_logPath, message);

                    if (hooks != null)
                    {
                        sendMessage(hooks.Item1, hooks.Item2, $"编号={emailtempInfo.Id}  名称={emailtempInfo.Name}  的报表发送失败,请及时处理", $"出错原因:{result.Item3}", $"{hooks.Item3}/etlogs/{etId}/");
                    }
                    return;
                }
                Log.WriteLine($"数据库执行成功，正在生成邮件数据");
                Log.WriteLine($"正在准备Stmp服务器，邮件等待发送");
                var stmpconfig = dataHelper.GetMailSender(emailtempInfo.MailSenderId);
                Log.WriteLine($"获取SMTP服务器");
                if (stmpconfig == null)
                {
                    Log.WriteLine($"Stmp服务器未获取到，无法发送邮件");
                    return;
                }
                //解密Smtp 邮箱密码
                Log.WriteLine($"解密Smtp 邮箱密码");
                var smtpPwd = StringHelper.DeEncrypt(stmpconfig.Password);
                Log.WriteLine($"SMTP准备完成");
                SmtpSetting setting = new SmtpSetting() { Authentication = stmpconfig.Authentication, Name = stmpconfig.Name, Server = stmpconfig.Server, Password = smtpPwd, Port = stmpconfig.Port, User = stmpconfig.User };
                var isRetry2 = 3;
                retrypoint2:
                try
                {
                    var cssHtml = "<style>.table-c table{border-right:1px solid #000;border-bottom:1px solid #000}.table-c table td{border-left:1px solid #000;border-top:1px solid #000;padding:5px 5px 5px 5px;}.table-c table th{border-left:1px solid #000;border-top:1px solid #000;font-weight:bold;background-color:rgb(217,236,255)} a[href=\"https://froala.com/wysiwyg-editor\"],a[href=\"https://www.froala.com/wysiwyg-editor?k=u\"]{display:none!important;position:absolute;top:-99999999px}</style>";
                    var signTag = "<p><em>该邮件由程序自动化处理，如对数据有疑问，请及时联系以下人员。</em></p><p>~~~~~~~~~~~~~~~~~~~~~~~~</p><p>Name:大数据组</p><p>Department：开发部</p><p>Company：可得集团</p><p>~~~~~~~~~~~~~~~~~~~~~~~~~~~</p>";
                    var bodys = cssHtml + emailtempInfo.Body;
                    if (emailtempInfo.DataAttachmentType == AttachmentType.AttachFile)
                    {
                        Log.WriteLine($"生成xlsx数据文件");
                        string filepaths = Data2ExeclHelper.DataSet2Execl(result.Item1);
                        //判断邮件大小
                        //如果超出预设大小,启用邮件中转站功能
                        Thread.Sleep(3 * 1000);
                        if (File.Exists(filepaths))
                        {
                            var file = new FileInfo(filepaths);
                            var mb = file.Length / 1e6;
                            if (mb > 45)
                            {
                                //启用 邮件中转站

                                Log.WriteLine($"开启文件中转站");
                                var filename = file.Name;
                                var code = Helper.StringHelper.Getcode(6, true);
                                var t = DateTimeHelper.GetLongbyTime(DateTime.Now.AddDays(29));
                                var vn = MatchCode(t.ToString(), filename, code);
                                var _strlink = $"{linkurl}?f={file.Name}&t={t}&vn={vn}";
                                bodys += $"<p>由于文件较大,无法直接送达.请点击此链接下载文件</p> <p> <a href=\"{_strlink}\">{_strlink}</a></p>   <p>提取密码:  <span style=\"font-size:28px;color:red;\">{code}</span></p>";
                                MailSenderHelper.Send(setting, emailtempInfo.Recipients, emailtempInfo.CC, emailtempInfo.BCC, emailtempInfo.Subject, bodys + signTag);
                            }
                            else
                            {
                                //发送邮件
                                MailSenderHelper.Send(setting, emailtempInfo.Recipients, emailtempInfo.CC, emailtempInfo.BCC, emailtempInfo.Subject, bodys + signTag, new[] { filepaths });
                            }
                        }

                    }
                    else
                    {

                        //内嵌HTML代码
                        Log.WriteLine($"生成Html邮件");
                        var tableHtml = DataSet2Html(result.Item1);
                        //  Log.WriteLine($"{JsonHelper.ToJsonString(tableHtml)}");
                        foreach (KeyValuePair<string, string> _pinfo in tableHtml)
                        {
                            bodys = bodys.Replace($"#{_pinfo.Key}#", _pinfo.Value);
                        }
                        //填充 一段话,文件中转站
                        Log.WriteLine($"邮件开始发送");
                        MailSenderHelper.Send(setting, emailtempInfo.Recipients, emailtempInfo.CC, emailtempInfo.BCC, emailtempInfo.Subject, bodys);
                    }
                    var times = new DateTime((DateTime.Now - start).Ticks);
                    Log.WriteLine($"邮件发送完成 共计耗时:{times:HH:mm:ss}", ConsoleColor.White, false);
                    dataHelper.AddEmailTask_Logs(new EmailTask_Logs() { TaskId = emailtempInfo.Id, Status = Status.Default, Centent = $"{emailtempInfo.Name}报表  发送完成", CreateTime = DateTime.Now });

                }
                catch(Exception ex)
                {
                    if (isRetry2 >= 1)
                    {
                        Log.WriteLine($" MailSenderHelper.Send 往邮件服务器发送失败 3s 后重新尝试 \n ex{ex.Message}  {ex.StackTrace} {DateTime.Now:HH:mm:ss}", ConsoleColor.White, false);
                        Thread.Sleep(3 * 1000);
                        goto retrypoint2;
                    }
                    isRetry2--;
       

            }
               
            }
            catch (Exception ex)
            {
                if (hooks != null)
                {
                    sendMessage(hooks.Item1, hooks.Item2, $"编号={emailtempInfo.Id}  名称={emailtempInfo.Name}  的报表发送失败,请及时处理", $"出错原因:{ex.Message}", $"{hooks.Item3}/etlogs/{etId}/");
                }
                Log.WriteLine($"邮件发送异常\r\n Message:{ex.Message} \r\n Source:{ex.Source} \r\n StackTrace:{ex.StackTrace}  \r\n HelpLink：{ex.HelpLink}   \r\n HResult：{ex.HResult} ");
            }
           
        }

        /// <summary>
        /// 发送钉钉消息
        /// </summary>
        /// <param name="isOpen"></param>
        /// <param name="hookUrl"></param>
        /// <param name="message"></param>
        /// <param name="errorMessage"></param>
        /// <param name="messageUrl"></param>
        private static void sendMessage(bool isOpen, string hookUrl, string message, string errorMessage, string messageUrl)
        {
            if (isOpen)
            {
               
                var content = new { msgtype = "link", link = new { text = errorMessage, title = "大数据组:" + message, picUrl = "", messageUrl = messageUrl } };
                var result = Post(hookUrl, JsonHelper.ToJsonString(content));
            }
        }
        private static string Post(string url, string content, string contentType = "application/json")
        {
            try
            {
                using (HttpClient _HttpClient = new HttpClient())
                {
                    
                    var httpcontent = new StringContent(content, Encoding.UTF8, contentType);
                    var response = _HttpClient.PostAsync(url, httpcontent).Result;
                    if (response.StatusCode != HttpStatusCode.OK)
                        return $"HTTP请求错误:{response.StatusCode}";
                    var result = response.Content.ReadAsStringAsync().Result;
                    return result;
                }
            }
            catch (Exception e)
            {
                return e.ToString();
            }
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="ds"></param>
        /// <returns> 第一个key 是名字 第二个 是 Table Html</returns>
        private static IList<KeyValuePair<string, string>> DataSet2Html(DataSet ds)
        {
            Log.WriteLine("开始执行DataSet2Html操作");
            IList<KeyValuePair<string, string>> result = new List<KeyValuePair<string, string>>();
            try
            {
                Log.WriteLine($"ds!=null:{ds == null} tablecount:{ds.Tables.Count}");
                if (ds == null || ds.Tables.Count < 1)
                {
                    return result;
                }

                for (var i = 0; i < ds.Tables.Count; i++)
                {
                    var _tables = ds.Tables[i];
                    var _sheetName = _tables.TableName;
                    Log.WriteLine($"Tables[{i}].Name={_sheetName}");
                    if (string.IsNullOrEmpty(_sheetName))
                    {
                        _sheetName = $"sheet{i + 1}";
                    }
                    var _html = DataTable2Html(_tables);
                    result.Add(new KeyValuePair<string, string>(_sheetName, _html));

                }
                return result;

            }
            catch (Exception ex)
            {

                Log.WriteLine($"DataSet2Html操作 异常{ex.Message} \r\n{ex.StackTrace}");
                return result;
            }


        }
        private static void FileWriteLine(string filename, string contents)
        {

            using (StreamWriter file = new StreamWriter(filename, true))
            {
                file.WriteLine(contents);
            }
        }

        private static string DataTable2Html(DataTable dt)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("<div class='table-c' style='margin-top:50px;'>");
            sb.Append("<table border='0' cellspacing='0' cellpadding='0'>");
            sb.Append("<tr>");
            foreach (DataColumn column in dt.Columns)
            {
                sb.Append("<th>" + column.ColumnName + "</th>");
            }
            sb.Append("</tr>");
            int iColsCount = dt.Columns.Count;
            int rowsCount = dt.Rows.Count - 1;
            for (int j = 0; j <= rowsCount; j++)
            {
                sb.Append("<tr>");
                // sb.Append("<td>" + ((int)(j + 1)).ToString() + "</td>");
                for (int k = 0; k <= iColsCount - 1; k++)
                {
                    sb.Append("<td>");
                    object obj = dt.Rows[j][k];
                    if (obj == DBNull.Value)
                    {
                        obj = "&nbsp;";//如果是NULL则在HTML里面使用一个空格替换之
                    }
                    if (obj.ToString() == "")
                    {
                        obj = "&nbsp;";
                    }
                    string strCellContent = obj.ToString().Trim();
                    sb.Append(strCellContent);
                    sb.Append("</td>");
                }
                sb.Append("</tr>");
            }
            sb.Append("</table></div>");
            return sb.ToString();
        }


    }





}
